{==============================================================================|
| Project : Graham Algorithm (Bayessian filter)                     | 24.05.09 |
|==============================================================================|
| Content: SQLite3 storage                                                     |
|==============================================================================|
| Copyright (c) 2009, Denis Shemanayev                                         |
| All rights reserved.                                                         |
|                                                                              |
| Redistribution and use in source and binary forms, with or without           |
| modification, are permitted provided that the following conditions are met:  |
|                                                                              |
| Redistributions of source code must retain the above copyright notice, this  |
| list of conditions and the following disclaimer.                             |
|                                                                              |
| Redistributions in binary form must reproduce the above copyright notice,    |
| this list of conditions and the following disclaimer in the documentation    |
| and/or other materials provided with the distribution.                       |
|                                                                              |
| Neither the name of Denis Shemanayev nor the names of its contributors may   |
| be used to endorse or promote products derived from this software without    |
| specific prior written permission.                                           |
|                                                                              |
| THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS"  |
| AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE    |
| IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE   |
| ARE DISCLAIMED. IN NO EVENT SHALL THE REGENTS OR CONTRIBUTORS BE LIABLE FOR  |
| ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL       |
| DAMAGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR   |
| SERVICES; LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER   |
| CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT           |
| LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY    |
| OUT OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH  |
| DAMAGE.                                                                      |
|==============================================================================|
| Contacts:                                                                    |
|           E-Mail: nis [at] nis [dot] org [dot] ua                            |
|           WWW:    http://nis.org.ua                                          |
|==============================================================================}
{: @abstract(SQLite3 storage implementation)
Stores all words in SQLite3 database.
}

unit uGrahamStorageSQLite;

interface

uses
  uGrahamAlgorithm, SQLite3,
 {$IFNDEF NOT_USE_TNT}
  TntClasses, TntSysUtils
 {$ELSE}
  WideClasses, WideSysUtils
 {$ENDIF};

type
  TGrahamStorageSQLite = class(TGrahamStorage)
  private
    FSQL: TSQLiteDB;
  protected
    function GetHamWordFreq(const Word: WideString): Integer; override;
    function GetSpamWordFreq(const Word: WideString): Integer; override;
    function GetMsgNumberHam: Integer; override;
    function GetMsgNumberSpam: Integer; override;
    function GetProbability(const Word: WideString): Extended; override;
    procedure SetHamWordFreq(const Word: WideString; AValue: Integer); override;
    procedure SetSpamWordFreq(const Word: WideString; AValue: Integer); override;
    procedure SetMsgNumberHam(AValue: Integer); override;
    procedure SetMsgNumberSpam(AValue: Integer); override;
    procedure SetProbability(const Word: WideString; AValue: Extended); override;
  public
    constructor Create(const FileName: WideString); override;
    destructor Destroy; override;
    procedure GetHamWords(const Words: {$IFNDEF NOT_USE_TNT}TTntStrings{$ELSE}TWStrings{$ENDIF}); override;
    procedure GetSpamWords(const Words: {$IFNDEF NOT_USE_TNT}TTntStrings{$ELSE}TWStrings{$ENDIF}); override;    
  end;

implementation

uses
  SysUtils {$IFDEF DEBUG}, uDebug{$ENDIF};

{ ************************************************************* }
function PrepareStr(const Str: WideString): UTF8String;
begin
  Result := UTF8Encode(Str);
end;

{ TGrahamStorageSQLite }

{ ************************************************************* }
constructor TGrahamStorageSQLite.Create(const FileName: WideString);
const
  C_CREATE_1: WideString = 'CREATE TABLE graham_words_ham (id INTEGER PRIMARY KEY, word TEXT UNIQUE, frequency INTEGER)';
  C_CREATE_2: WideString = 'CREATE TABLE graham_words_spam (id INTEGER PRIMARY KEY, word TEXT UNIQUE, frequency INTEGER)';
  C_CREATE_3: WideString = 'CREATE TABLE graham_words_probability (id INTEGER PRIMARY KEY, word TEXT UNIQUE, probability TEXT)';
  C_CREATE_4: WideString = 'CREATE TABLE graham_stats (id INTEGER PRIMARY KEY, ham_learned INTEGER, spam_learned INTEGER)';
var
  Err: PAnsiChar;
  s: UTF8String;
begin
  inherited;
  {$IFDEF DEBUG}
   DLog('GrahamStorageSQLite: Create');
  {$ENDIF}
  _SQLite3_Open(PAnsiChar(PrepareStr(FileName)), FSQL);
  if GetMsgNumberSpam = 0 then
  begin
    s := PrepareStr(C_CREATE_1);
    _SQLite3_Exec(FSQL, PAnsiChar(s), nil, nil, Err);
    _SQLite3_Free(Err);
    s := PrepareStr(C_CREATE_2);
    _SQLite3_Exec(FSQL, PAnsiChar(s), nil, nil, Err);
    _SQLite3_Free(Err);
    s := PrepareStr(C_CREATE_3);
    _SQLite3_Exec(FSQL, PAnsiChar(s), nil, nil, Err);
    _SQLite3_Free(Err);
    s := PrepareStr(C_CREATE_4);
    _SQLite3_Exec(FSQL, PAnsiChar(s), nil, nil, Err);
    _SQLite3_Free(Err);
  end;
  {$IFDEF DEBUG}
   DLog('GrahamStorageSQLite: Create done.');
  {$ENDIF}
end;

{ ************************************************************* }
destructor TGrahamStorageSQLite.Destroy;
begin
  {$IFDEF DEBUG}
   DLog('GrahamStorageSQLite: Destroy');
  {$ENDIF}
  _SQLite3_Close(FSQL);
  inherited;
end;

{ ************************************************************* }
function TGrahamStorageSQLite.GetHamWordFreq(
  const Word: WideString): Integer;
const
  C_SQL_1: WideString = 'SELECT frequency FROM graham_words_ham WHERE word = ''%s''';
var
  Tail: PAnsiChar;
  Stmt: TSqliteStmt;
  s: UTF8String;
begin
  inherited;
  {$IFDEF DEBUG}
   DLog('GrahamStorageSQLite: GetHamWordFreq');
  {$ENDIF}
  s := PrepareStr(WideFormat(C_SQL_1, [Word]));
  _SQLite3_Prepare(FSQL, PAnsiChar(s), Length(s), Stmt, Tail);
  _SQLite3_Step(Stmt);
  Result := _SQLite3_Column_Int(Stmt, 0);
  _SQLite3_Finalize(Stmt);
  {$IFDEF DEBUG}
   DLog('GrahamStorageSQLite: GetHamWordFreq done.');
  {$ENDIF}
end;

{ ************************************************************* }
procedure TGrahamStorageSQLite.GetHamWords(const Words: {$IFNDEF NOT_USE_TNT}TTntStrings{$ELSE}TWStrings{$ENDIF});
const
  C_SQL_1: WideString = 'SELECT word FROM graham_words_ham';
var
  Err: PAnsiChar;
  s: UTF8String;
  i, iRows, iCols: Cardinal;
  Res, RecOffs: TSQLiteResult;
begin
  inherited;
  {$IFDEF DEBUG}
   DLog('GrahamStorageSQLite: GetHamWords');
  {$ENDIF}
  Words.Clear;
  Words.BeginUpdate;
  s := PrepareStr(C_SQL_1);
  _SQLite3_Get_Table(FSQL, PAnsiChar(s), Res, iRows, iCols, Err);
  _SQLite3_Free(Err);
  RecOffs := Res;
  Inc(RecOffs);  // skip header
  if iRows <> 0 then
  for i := 0 to iRows - 1 do
  begin
    Words.Add(UTF8Decode(RecOffs^));
    Inc(RecOffs);
  end;
  _SQLite3_Free_Table(Res);
  Words.EndUpdate;
  {$IFDEF DEBUG}
   DLog('GrahamStorageSQLite: GetHamWords done.');
  {$ENDIF}
end;

{ ************************************************************* }
function TGrahamStorageSQLite.GetMsgNumberHam: Integer;
const
  C_SQL_1: WideString = 'SELECT ham_learned FROM graham_stats';
var
  Tail: PAnsiChar;
  Stmt: TSqliteStmt;
  s: UTF8String;
begin
  inherited;
  {$IFDEF DEBUG}
   DLog('GrahamStorageSQLite: GetMsgNumberHam');
  {$ENDIF}
  s := PrepareStr(C_SQL_1);
  _SQLite3_Prepare(FSQL, PAnsiChar(s), Length(s), Stmt, Tail);
  _SQLite3_Step(Stmt);
  Result := _SQLite3_Column_Int(Stmt, 0);
  _SQLite3_Finalize(Stmt);
  {$IFDEF DEBUG}
   DLog('GrahamStorageSQLite: GetMsgNumberHam done.');
  {$ENDIF}
end;

{ ************************************************************* }
function TGrahamStorageSQLite.GetMsgNumberSpam: Integer;
const
  C_SQL_1: WideString = 'SELECT spam_learned FROM graham_stats';
var
  Tail: PAnsiChar;
  Stmt: TSqliteStmt;
  s: UTF8String;
begin
  inherited;
  {$IFDEF DEBUG}
   DLog('GrahamStorageSQLite: GetMsgNumberSpam');
  {$ENDIF}
  s := PrepareStr(C_SQL_1);
  _SQLite3_Prepare(FSQL, PAnsiChar(s), Length(s), Stmt, Tail);
  _SQLite3_Step(Stmt);
  Result := _SQLite3_Column_Int(Stmt, 0);
  _SQLite3_Finalize(Stmt);
  {$IFDEF DEBUG}
   DLog('GrahamStorageSQLite: GetMsgNumberSpam done.');
  {$ENDIF}
end;

{ ************************************************************* }
function TGrahamStorageSQLite.GetProbability(
  const Word: WideString): Extended;
const
  C_SQL_1: WideString = 'SELECT probability FROM graham_words_probability WHERE word = ''%s''';
var
  s, Tail: PAnsiChar;
  Stmt: TSqliteStmt;
  su: UTF8String;
begin
  inherited;
  {$IFDEF DEBUG}
   DLog('GrahamStorageSQLite: GetProbability');
  {$ENDIF}
  Result := 0.0;
  su := PrepareStr(WideFormat(C_SQL_1, [Word]));
  _SQLite3_Prepare(FSQL, PAnsiChar(su), Length(su), Stmt, Tail);
  _SQLite3_Step(Stmt);
  s := _SQLite3_Column_Text(Stmt, 0);
  if s <> nil then
    Result := StrToFloatDef(UTF8Decode(s), 0);
  _SQLite3_Finalize(Stmt);
  if Result = 0.0 then
    Result := C_DEFAULT_UNKNOWN_PROBABILITY;
  {$IFDEF DEBUG}
   DLog('GrahamStorageSQLite: GetProbability done.');
  {$ENDIF}
end;

{ ************************************************************* }
function TGrahamStorageSQLite.GetSpamWordFreq(
  const Word: WideString): Integer;
const
  C_SQL_1: WideString = 'SELECT frequency FROM graham_words_spam WHERE word = ''%s''';
var
  Tail: PAnsiChar;
  Stmt: TSqliteStmt;
  s: UTF8String;
begin
  inherited;
  {$IFDEF DEBUG}
   DLog('GrahamStorageSQLite: GetSpamWordFreq');
  {$ENDIF}
  s := PrepareStr(WideFormat(C_SQL_1, [Word]));
  _SQLite3_Prepare(FSQL, PAnsiChar(s), Length(s), Stmt, Tail);
  _SQLite3_Step(Stmt);
  Result := _SQLite3_Column_Int(Stmt, 0);
  _SQLite3_Finalize(Stmt);
  {$IFDEF DEBUG}
   DLog('GrahamStorageSQLite: GetSpamWordFreq done.');
  {$ENDIF}
end;

{ ************************************************************* }
procedure TGrahamStorageSQLite.GetSpamWords(const Words: {$IFNDEF NOT_USE_TNT}TTntStrings{$ELSE}TWStrings{$ENDIF});
const
  C_SQL_1: WideString = 'SELECT word FROM graham_words_spam';
var
  Err: PAnsiChar;
  s: UTF8String;
  i, iRows, iCols: Cardinal;
  Res, RecOffs: TSQLiteResult;
begin
  inherited;
  {$IFDEF DEBUG}
   DLog('GrahamStorageSQLite: GetSpamWords');
  {$ENDIF}
  Words.Clear;
  Words.BeginUpdate;
  s := PrepareStr(C_SQL_1);
  _SQLite3_Get_Table(FSQL, PAnsiChar(s), Res, iRows, iCols, Err);
  _SQLite3_Free(Err);
  RecOffs := Res;
  Inc(RecOffs);  // skip header
  if iRows <> 0 then
  for i := 0 to iRows - 1 do
  begin
    Words.Add(UTF8Decode(RecOffs^));
    Inc(RecOffs);
  end;
  _SQLite3_Free_Table(Res);
  Words.EndUpdate;
  {$IFDEF DEBUG}
   DLog('GrahamStorageSQLite: GetSpamWords done.');
  {$ENDIF}
end;

{ ************************************************************* }
procedure TGrahamStorageSQLite.SetHamWordFreq(const Word: WideString;
  AValue: Integer);
const
  C_SQL_1: WideString = 'INSERT OR REPLACE INTO graham_words_ham (word, frequency) VALUES (''%s'', %s)';
var
  Err: PAnsiChar;
  s: UTF8String;
begin
  inherited;
  {$IFDEF DEBUG}
   DLog('GrahamStorageSQLite: SetHamWordFreq');
  {$ENDIF}
  s := PrepareStr(WideFormat(C_SQL_1, [Word, IntToStr(AValue)]));
  _SQLite3_Exec(FSQL, PAnsiChar(s), nil, nil, Err);
  _SQLite3_Free(Err);
  {$IFDEF DEBUG}
   DLog('GrahamStorageSQLite: SetHamWordFreq done.');
  {$ENDIF}
end;

{ ************************************************************* }
procedure TGrahamStorageSQLite.SetMsgNumberHam(AValue: Integer);
const
  C_SQL_1: WideString = 'UPDATE graham_stats SET ham_learned = %s';
  C_SQL_2: WideString = 'INSERT INTO graham_stats (id, ham_learned, spam_learned) VALUES (0, %s, 0)';
var
  Err: PAnsiChar;
  s: UTF8String;
begin
  inherited;
  {$IFDEF DEBUG}
   DLog('GrahamStorageSQLite: SetMsgNumberHam');
  {$ENDIF}
  s := PrepareStr(WideFormat(C_SQL_2, [IntToStr(AValue)]));
  if _SQLite3_Exec(FSQL, PAnsiChar(s), nil, nil, Err) <> 0 then
  begin
    _SQLite3_Free(Err);
    s := PrepareStr(WideFormat(C_SQL_1, [IntToStr(AValue)]));
    _SQLite3_Exec(FSQL, PAnsiChar(s), nil, nil, Err);
  end; 
  _SQLite3_Free(Err);
  {$IFDEF DEBUG}
   DLog('GrahamStorageSQLite: SetMsgNumberHam done.');
  {$ENDIF}
end;

{ ************************************************************* }
procedure TGrahamStorageSQLite.SetMsgNumberSpam(AValue: Integer);
const
  C_SQL_1: WideString = 'UPDATE graham_stats SET spam_learned = %s';
  C_SQL_2: WideString = 'INSERT INTO graham_stats (id, ham_learned, spam_learned) VALUES (0, 0, %s)';
var
  Err: PAnsiChar;
  s: UTF8String;
begin
  inherited;
  {$IFDEF DEBUG}
   DLog('GrahamStorageSQLite: SetMsgNumberSpam');
  {$ENDIF}
  s := PrepareStr(WideFormat(C_SQL_2, [IntToStr(AValue)]));
  if _SQLite3_Exec(FSQL, PAnsiChar(s), nil, nil, Err) <> 0 then
  begin
    _SQLite3_Free(Err);
    s := PrepareStr(WideFormat(C_SQL_1, [IntToStr(AValue)]));
    _SQLite3_Exec(FSQL, PAnsiChar(s), nil, nil, Err);
  end;
  _SQLite3_Free(Err);
  {$IFDEF DEBUG}
   DLog('GrahamStorageSQLite: SetMsgNumberSpam done.');
  {$ENDIF}
end;

{ ************************************************************* }
procedure TGrahamStorageSQLite.SetProbability(const Word: WideString;
  AValue: Extended);
const
  C_SQL_1: WideString = 'INSERT OR REPLACE INTO graham_words_probability (word, probability) VALUES (''%s'', ''%s'')';
var
  Err: PAnsiChar;
  s: UTF8String;
begin
  inherited;
  {$IFDEF DEBUG}
   DLog('GrahamStorageSQLite: SetProbability');
  {$ENDIF}
  s := PrepareStr(WideFormat(C_SQL_1, [Word, FloatToStr(AValue)]));
  _SQLite3_Exec(FSQL, PAnsiChar(s), nil, nil, Err);
  _SQLite3_Free(Err);
  {$IFDEF DEBUG}
   DLog('GrahamStorageSQLite: SetProbability done.');
  {$ENDIF}
end;

{ ************************************************************* }
procedure TGrahamStorageSQLite.SetSpamWordFreq(const Word: WideString;
  AValue: Integer);
const
  C_SQL_1: WideString = 'INSERT OR REPLACE INTO graham_words_spam (word, frequency) VALUES (''%s'', %s)';
var
  Err: PAnsiChar;
  s: UTF8String;
begin
  inherited;
  {$IFDEF DEBUG}
   DLog('GrahamStorageSQLite: SetSpamWordFreq');
  {$ENDIF}
  s := PrepareStr(WideFormat(C_SQL_1, [Word, IntToStr(AValue)]));
  _SQLite3_Exec(FSQL, PAnsiChar(s), nil, nil, Err);
  _SQLite3_Free(Err);
  {$IFDEF DEBUG}
   DLog('GrahamStorageSQLite: SetSpamWordFreq done.');
  {$ENDIF}
end;

end.
